"""
Case Type   : security-schema
Case Name   : 外连接(+)，使用不同schema的相同表名进行连接
Create At   : 2024/7/25
Owner       : songjing20
Description :
    1.创建模式和表
    2.不同模式下的同名表连接查询
    3.删除模式和表
Expect      :
    1.成功
    2.查询成功
    3.成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant


class Privategrant(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.commonsh = CommonSH('PrimaryDbUser')
        self.Constant = Constant()
        self.schema_name = 'schema_0014'
        self.tb_name = 'table_0014'

    def test_copy_file(self):
        text = '-----step1:创建测试表并插入数据;expect:成功-----'
        self.log.info(text)
        preset_res = self.commonsh.execut_db_sql(
            f"drop schema if exists {self.schema_name} cascade;"
            f"create schema {self.schema_name};"
            f"create table public.{self.tb_name}(col int);"
            f"create table {self.schema_name}.{self.tb_name}(col int);")
        self.log.info(preset_res)
        self.assertIn(self.Constant.CREATE_SCHEMA_SUCCESS_MSG, preset_res,
                      f'执行失败:{text}')
        self.assertEqual(preset_res.count(self.Constant.CREATE_TABLE_SUCCESS),
                         2, f'执行失败:{text}')

        text = '-----step2:不同模式下的同名表连接查询expect:查询成功-----'
        self.log.info(text)
        res = self.commonsh.execut_db_sql(
            f"select * "
            f"from public.{self.tb_name}, {self.schema_name}.{self.tb_name} "
            f"where public.{self.tb_name}.col(+)="
            f"{self.schema_name}.{self.tb_name}.col;"
            f"select * "
            f"from public.{self.tb_name} t1,{self.schema_name}.{self.tb_name} "
            f"t2 where t1.col (+)=t2.col;")
        self.log.info(res)
        self.assertEqual(res.count('(0 rows)'), 2, f'执行失败:{text}')

    def tearDown(self):
        text = '-----step3:删除表;expect:成功-----'
        self.log.info(text)
        res = self.commonsh.execut_db_sql(
            f"drop schema {self.schema_name} cascade;"
            f"drop table {self.tb_name} cascade")
        self.log.info(res)
        self.assertTrue(self.Constant.DROP_SCHEMA_SUCCESS_MSG in res and
                    self.Constant.TABLE_DROP_SUCCESS in res, f'执行失败:{text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
